﻿Imports System.Data.OleDb

Public Class Quiz_RatingManagement
    Inherits ConnectionClass

    Public Function SelectAVGQuizRatingByQuizId(ByVal quizId As Int32) As Double
        Try

        
        myConnection.Open()
        Dim Cmd As OleDbCommand = New OleDbCommand("SELECT AVG(Ratings.RatingValue) AS AverageRating FROM  (Quizes_Ratings INNER JOIN Ratings ON Quizes_Ratings.RatingId = Ratings.RatingId)WHERE  (Quizes_Ratings.QuizId = @f1) GROUP BY Quizes_Ratings.QuizId", myConnection)
        Cmd.Parameters.AddWithValue("@f1", quizId)
        Dim dr = Cmd.ExecuteReader()
        If (dr.HasRows) Then
            dr.Read()
            Dim quizAverage = dr.GetValue(0)
            Return quizAverage

        Else
            Return 0
        End If

            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function SelectCountUserIdQuizId(ByVal userId As Int32, ByVal quizId As Int32) As Int32
        Try


            myConnection.Open()
            Dim Cmd As OleDbCommand = New OleDbCommand("Select Count(Quiz_RatingId) from Quizes_Ratings where QuizId = @f1 and UserId =@f2 Group By QuizId", myConnection)
            Cmd.Parameters.AddWithValue("@f1", quizId)
            Cmd.Parameters.AddWithValue("@f2", userId)
            Dim dr = Cmd.ExecuteReader()
            If (dr.HasRows) Then
                dr.Read()
                Dim countQuizRating = dr.GetValue(0)
                Return countQuizRating
            Else
                Return 0
            End If

            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub InsertQuizRating(ByVal ratingID As Int32, ByVal quizId As Int32, ByVal userId As Int32)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT INTO Quizes_Ratings(RatingId,QuizId,UserId) VALUES(@f1,@f2,@f3)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", ratingID)
            newCmd.Parameters.AddWithValue("@f2", quizId)
            newCmd.Parameters.AddWithValue("@f3", userId)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub DeleteQuizRatingByQuizIdUserId(ByVal quizId As Int32, ByVal userId As Int32)
        Try

        
        myConnection.Open()
        Dim SqlString As String = "DELETE FROM Quizes_Ratings where QuizId =@f1 and UserId =@f2"
        Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        newCmd.CommandType = CommandType.Text
        newCmd.Parameters.AddWithValue("@f1", quizId)
        newCmd.Parameters.AddWithValue("@f2", userId)
        newCmd.ExecuteNonQuery()
        myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectHighestRatingQuiz() As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  AVG(Ratings.RatingValue) AS AverageRating, Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture FROM            ((Quizes_Ratings RIGHT OUTER JOIN  Quizes ON Quizes_Ratings.QuizId = Quizes.QuizId) LEFT OUTER JOIN  Ratings ON Quizes_Ratings.RatingId = Ratings.RatingId) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture ORDER BY AVG(Ratings.RatingValue) DESC"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectHighestRatingQuizByTagId(ByVal tagId As Int32) As OleDbDataReader
        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT AVG(Ratings.RatingValue) AS AverageRating, Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture FROM (((Quiz_Tags INNER JOIN Quizes ON Quiz_Tags.QuizId = Quizes.QuizId) LEFT OUTER JOIN Quizes_Ratings ON Quizes.QuizId = Quizes_Ratings.QuizId) LEFT OUTER JOIN Ratings ON Quizes_Ratings.RatingId = Ratings.RatingId) WHERE (Quiz_Tags.TagId = @f1) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, Quiz_Tags.TagId ORDER BY AVG(Ratings.RatingValue) DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", tagId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
